package itsm.isperp.module.service.slm;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.utils.DateUtils;

import java.text.ParseException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.jdbc.support.DatabaseType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 服务协议手工作业的相关服务
 * 
 * @author lizx
 * 
 */
@Service
public class SlmServiceAgreementManualService {

	public static final Logger logger = LoggerFactory
			.getLogger(SlmServiceAgreementManualService.class);

	/**
	 * 重置最终期限
	 * 
	 * @param tableName
	 * @param columnName
	 * @param fromDateColumn
	 * @param otherCondition
	 * @throws Exception
	 */
	public static void resetDeadline(String tableName, String columnName,
			String fromDateColumn, String otherCondition) throws Exception {

		String sql = "SELECT id,service_category_id,slm_id," + fromDateColumn
				+ ",are_vip FROM " + tableName + " WHERE 1=1 AND "
				+ otherCondition;

		// 加载服务目录条件，1、非其它类型的服务；
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		
		String sqlt="SELECT b.* FROM proj_project a INNER JOIN app_calendar_config b ON a.app_calendar_config_id=b.id WHERE ROWNUM=1";
		if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) 
			sqlt="SELECT b.* FROM proj_project a INNER JOIN app_calendar_config b ON a.app_calendar_config_id=b.id limit 1";
		Map<String, Object> calMap = jdbcTemplate
				.queryForMap(sqlt);

		String app_calendar_config_id = calMap.get("id").toString();

		long am_begin = ((Date) calMap.get("am_begin")).getTime();
		long am_end = ((Date) calMap.get("am_end")).getTime();

		long pm_begin = ((Date) calMap.get("pm_begin")).getTime();
		long pm_end = ((Date) calMap.get("pm_end")).getTime();

		for (Map<String, Object> map : list) {
			String id = (String) map.get("id");
			String service_category_id = (String) map
					.get("service_category_id");
			// String slm_id = (String) map.get("slm_id");

			Date now_date = (Date) map.get(fromDateColumn);

			String nowSql = "to_date('" + DateUtils.formatDateTime(now_date)
					+ "','yyyy-MM-dd HH24:mi')";

			Object are_vip_obj = map.get("are_vip");
			int are_vip = 0;
			if (are_vip_obj != null) {
				are_vip = Integer.parseInt(are_vip_obj.toString());
			}
			String user_type = "";
			if (are_vip == 0) {
				user_type = "普通用户";
			} else {
				user_type = "VIP";
			}

			String s = "1=1  AND ( user_type='" + user_type
					+ "' ) AND (service_category_id='" + service_category_id
					+ "' )";

			String getAgreement = "SELECT id,name,use_project_calendar FROM slm_service_agreement WHERE scope_sql=?   AND elapsed_type='TEL' AND rownum=1";
			if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) 
				getAgreement = "SELECT id,name,use_project_calendar FROM slm_service_agreement WHERE scope_sql=?   AND elapsed_type='TEL' limit 1";

			List<Map<String, Object>> list2 = jdbcTemplate.queryForList(
					getAgreement, s);
			String argeement_id = null;
			String argeement_name = null;
			if (list2.size() == 1) {
				Map<String, Object> m = list2.get(0);

				// slm_service_target
				argeement_id = m.get("id").toString();
				argeement_name = m.get("name").toString();

			}
			try {
				double target_sec_all = jdbcTemplate.queryForObject(
						"SELECT SUM(target_sec) FROM slm_service_target WHERE agreement_id='"
								+ argeement_id + "'", Double.class);

				Date fiDate = getDeadline(argeement_id, target_sec_all * 1000,
						app_calendar_config_id, am_begin, am_end, pm_begin,
						pm_end, now_date, nowSql, jdbcTemplate);
				String up = "UPDATE " + tableName + " SET slm_id='"
						+ argeement_id + "',slm_name='" + argeement_name
						+ "',deadline=?  WHERE id='" + id + "'";

				jdbcTemplate.update(up, fiDate);
			} catch (Exception e) {
				String up = "UPDATE " + tableName + " SET slm_id='"
						+ argeement_id + "',slm_name='" + argeement_name
						+ "',deadline=null  WHERE id='" + id + "'";
				jdbcTemplate.update(up);
			}

		}

	}

	/**
	 * 获取最终期限
	 * 
	 * @param argeement_id
	 * @param target_sec_all
	 * @param app_calendar_config_id
	 * @param am_begin
	 * @param am_end
	 * @param pm_begin
	 * @param pm_end
	 * @param now_date
	 * @param nowSql
	 * @param jdbcTemplate
	 * @return
	 * @throws ParseException
	 */
	public static Date getDeadline(String argeement_id, double target_sec_all,
			String app_calendar_config_id, long am_begin, long am_end,
			long pm_begin, long pm_end, Date now_date, String nowSql,
			JdbcTemplate jdbcTemplate) throws ParseException {

		double milsec_d = ((am_end - am_begin) + (pm_end - pm_begin));

		long sd = DateUtils.parseDate("1970-01-01").getTime();

		long lll3 = (am_begin - sd);
		long lll31 = (am_end - sd);
		long lll4 = (pm_begin - sd);
		long lll41 = (pm_end - sd);

		long l1 = (am_end - am_begin);
		long l2 = pm_end - pm_begin;

		//
		long nt = DateUtils.parseDate(DateUtils.formatDate(now_date)).getTime();
		// 时间毫秒数
		long now_time = now_date.getTime() - nt;

		long target_sec_all_y = Double.valueOf(target_sec_all % milsec_d)
				.longValue();

		// 无论如何，需要多取一天
		int ii = Double.valueOf(Math.ceil(target_sec_all / milsec_d))
				.intValue();

		String sql = "SELECT * FROM( SELECT t.*, ROWNUM as ROWNUM_ FROM ( "
				+ "SELECT calendar_date FROM app_calendar_day a "
				+ " WHERE TRUNC(calendar_date)>=TRUNC("
				+ nowSql
				+ ") AND status=1 AND app_calendar_config_id='"
				+ app_calendar_config_id
				+ "'  ORDER BY calendar_date ASC)t WHERE ROWNUM >0 AND ROWNUM<="
				+ (ii + 1) + " ) WHERE ROWNUM_=1 OR ROWNUM_>=" + ii;
		if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {
			sql="SELECT t.* FROM(SELECT calendar_date  FROM app_calendar_day a "
		               +" WHERE calendar_date >= "+nowSql+" AND STATUS = 1 " 
		               +" AND app_calendar_config_id = '"+app_calendar_config_id+"' "
		               +" ORDER BY calendar_date ASC) t "
		               +" WHERE 1=1 LIMIT "+(ii + 1)+"";
		} 
		// List<Map<String, Object>> days =
		// jdbcTemplate.queryForList(sql);
		// now==thi

		List<Map<String, Object>> listDays = jdbcTemplate.queryForList(sql);

		Map<String, Object> firstD = listDays.get(0);

		Map<String, Object> middleD = null;

		if (listDays.size() == 2) {
			middleD = listDays.get(listDays.size() - 1);
		} else {
			middleD = listDays.get(listDays.size() - 2);
		}

		Map<String, Object> lastD = listDays.get(listDays.size() - 1);

		Date firstDate = (Date) firstD.get("calendar_date");
		// long firstTime = (firstDate).getTime();
		long middleTime = ((Date) middleD.get("calendar_date")).getTime();
		long lastTime = ((Date) lastD.get("calendar_date")).getTime();

		Date fiDate = null;

		long deadDateTime = 0;// 最后期限的日期
		long haveTime = 0;// 剩余时间
		long endTime = 0;
		if (target_sec_all_y == 0) {

			deadDateTime = middleTime;
			endTime = lll41;
		} else {

			// 今天是工作日
			if (now_time > lll31) {
				// 下午工作时间
				haveTime = target_sec_all_y - (lll41 - now_time);
			} else if (now_time >= lll3) {
				// 上午工作时间
				haveTime = target_sec_all_y - l2 - (lll31 - now_time);
			} else {
				haveTime = target_sec_all_y - (l1 + l2);
			}

			if (haveTime <= 0) {

				// 需要回退一些时间
				// 需要在最后一天补回去的时间
				long haveTimeAbs = Math.abs(haveTime);

				if (haveTimeAbs > l2) {
					endTime = lll3 + (l1 - (haveTimeAbs - l2));
				} else {
					endTime = lll4 + (l2 - haveTimeAbs);
				}

			} else {

				if (haveTime > l1) {
					endTime = lll4 + (l2 - (haveTime - l1));
				} else {
					endTime = lll3 + haveTime;
				}
			}

			if (target_sec_all < milsec_d && haveTime <= 0) {
				deadDateTime = firstDate.getTime();
			} else {

				// 如果总的剩余时间
				if (target_sec_all_y > haveTime) {
					deadDateTime = lastTime;
				} else {
					deadDateTime = middleTime;
				}

			}

		}

		fiDate = new Date(deadDateTime + endTime);
		return fiDate;
	}

	/**
	 * 重置两个事件点的时间差，并更新到指定的字段中
	 * 
	 * @param tableName
	 * @param fromDateColumn
	 * @param toDateColumn
	 * @param timeDistanceColumn
	 * @param otherCondition
	 */
	public static void resetTimeDuration(String tableName,
			String fromDateColumn, String toDateColumn,
			String timeDistanceColumn, String otherCondition) {

		String sql = "SELECT  id," + fromDateColumn + "," + toDateColumn
				+ "  FROM " + tableName + " WHERE 1=1 AND " + otherCondition;

		// 加载服务目录条件，1、非其它类型的服务；
		JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

		Map<String, Object> calMap = jdbcTemplate
				.queryForMap("SELECT b.* "
						+ "  FROM proj_project a INNER JOIN app_calendar_config b ON a.app_calendar_config_id=b.id WHERE ROWNUM=1 ");

		String app_calendar_config_id = calMap.get("id").toString();

		long am_begin = ((Date) calMap.get("am_begin")).getTime();
		long am_end = ((Date) calMap.get("am_end")).getTime();

		long pm_begin = ((Date) calMap.get("pm_begin")).getTime();
		long pm_end = ((Date) calMap.get("pm_end")).getTime();

		for (Map<String, Object> map : list) {

			String id = (String) map.get("id");
			Date now_date = (Date) map.get(fromDateColumn);
			Date to_date = (Date) map.get(toDateColumn);

			if (to_date == null) {
				continue;
			}

			String nowSql = "to_date('" + DateUtils.formatDateTime(now_date)
					+ "','yyyy-MM-dd HH24:mi')";

			String toSql = "to_date('" + DateUtils.formatDateTime(to_date)
					+ "','yyyy-MM-dd HH24:mi')";

			try {

				int timeLong = (int) (getTimeDuration(app_calendar_config_id,
						am_begin, am_end, pm_begin, pm_end, now_date, nowSql,
						to_date, toSql, jdbcTemplate) / 1000 / 60);

				String up = "UPDATE " + tableName + " SET "
						+ timeDistanceColumn + "=" + timeLong + " WHERE id='"
						+ id + "'";

				jdbcTemplate.update(up);

				// System.out.println(timeLong);

			} catch (Exception e) {

				logger.error(e.getMessage(), e);
			}

		}
	}

	/**
	 * start和end在指定工作时间内的时间差
	 * 
	 * @param start
	 * @param end
	 * @param ambegin
	 * @param amend
	 * @param pmbegin
	 * @param pmend
	 * @return
	 */
	private static long getTowTimeDuration(long start, long end, long ambegin,
			long amend, long pmbegin, long pmend) {

		if (start >= end) {
			return 0;
		}

		// 不在工作时间内返回0
		if (start >= pmend || end <= ambegin) {
			return 0;
		} else if (start >= pmbegin) {// 如果开始时间在下午
			if (end >= pmend) {// 结束时间在下午以外
				return (pmend - start);
			} else {// 结束时间在下午以内
				return (end - start);
			}

		} else if (start >= amend) {// 开始于中午休息
			if (end >= pmend) {// 结束时间在下午以外
				return (pmend - pmbegin);
			} else if (end > pmbegin) {// 结束时间在下午以内
				return (end - pmbegin);
			} else {
				return 0;
			}
		} else {
			long ss = 0;
			if (start >= ambegin) {
				ss = start;
			} else {
				ss = ambegin;
			}
			if (end >= pmend) {// 开始与上午
				return (pmend - pmbegin) + (amend - ss);

			} else if (end >= pmbegin) {// 结束时间在下午以外
				return (end - pmbegin) + (amend - ss);
			} else if (end >= amend) {
				return (amend - ss);
			} else {
				return end - start;
			}
		}
	}

	/**
	 * 获取两个时间在指定工作日历中的时间差（按工作时间计算）
	 * 
	 * @param app_calendar_config_id
	 * @param am_begin
	 * @param am_end
	 * @param pm_begin
	 * @param pm_end
	 * @param now_date
	 * @param nowSql
	 * @param to_date
	 * @param toSql
	 * @param jdbcTemplate
	 * @return
	 * @throws ParseException
	 */
	private static long getTimeDuration(String app_calendar_config_id,
			long am_begin, long am_end, long pm_begin, long pm_end,
			Date now_date, String nowSql, Date to_date, String toSql,
			JdbcTemplate jdbcTemplate) throws ParseException {

		long sd = DateUtils.parseDate("1970-01-01").getTime();
		long ambegin = (am_begin - sd);
		long amend = (am_end - sd);
		long pmbegin = (pm_begin - sd);
		long pmend = (pm_end - sd);

		long nt = DateUtils.parseDate(DateUtils.formatDate(now_date)).getTime();
		// 时间毫秒数
		long now_time = now_date.getTime() - nt;

		long tt = DateUtils.parseDate(DateUtils.formatDate(to_date)).getTime();
		long to_time = to_date.getTime() - tt;

		String sql = "SELECT calendar_date FROM app_calendar_day a "
				+ " WHERE TRUNC(calendar_date)>=TRUNC(" + nowSql
				+ ") AND TRUNC(calendar_date)<=TRUNC(" + toSql
				+ ") AND status=1 AND app_calendar_config_id='"
				+ app_calendar_config_id + "'  ORDER BY calendar_date ASC";

		List<Map<String, Object>> listDays = jdbcTemplate.queryForList(sql);

		int intDays = listDays.size();
		if (intDays == 0) {
			return 0;
		}

		long sec = 0;

		Map<String, Object> firstD = listDays.get(0);
		Map<String, Object> lastD = listDays.get(intDays - 1);
		Date firstDate = (Date) firstD.get("calendar_date");
		Date lastDate = (Date) lastD.get("calendar_date");

		String strFirstDate = DateUtils.formatDate(firstDate);

		if (intDays == 1) {
			if (DateUtils.formatDate(now_date).equals(strFirstDate)) {

				if (DateUtils.formatDate(to_date).equals(strFirstDate)) {
					sec = getTowTimeDuration(now_time, to_time, ambegin, amend,
							pmbegin, pmend);
				} else {
					sec = getTowTimeDuration(now_time, pmend, ambegin, amend,
							pmbegin, pmend);
				}

			} else if (DateUtils.formatDate(to_date).equals(strFirstDate)) {
				sec = getTowTimeDuration(ambegin, to_time, ambegin, amend,
						pmbegin, pmend);
			} else {
				sec = (pmbegin - ambegin) + (pmend - pmbegin);
			}

		} else {
			int intDaysDel = 0;
			long sec1 = 0;
			long sec2 = 0;
			if (DateUtils.formatDate(now_date).equals(strFirstDate)) {
				sec1 = getTowTimeDuration(now_time, pmend, ambegin, amend,
						pmbegin, pmend);
				intDaysDel += 1;
			}

			if (DateUtils.formatDate(to_date).equals(
					DateUtils.formatDate(lastDate))) {
				sec2 = getTowTimeDuration(ambegin, to_time, ambegin, amend,
						pmbegin, pmend);
				intDaysDel += 1;
			}
			sec = ((amend - ambegin) + (pmend - pmbegin))
					* (intDays - intDaysDel) + (sec1 + sec2);
		}

		return sec;
	}

}
